﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Common;
using Musicman.Common.Entities;
using System.Data.Common;
using System.IO;

namespace Musicman.DAL
{
    public class UserDAO : DAOBase
    {
        # region singleton definition
        static readonly UserDAO instance = new UserDAO();

        public static UserDAO Instance
        {
            get
            {
                return instance;
            }
        }
        static UserDAO()
        {
        }
        # endregion

        #region select

        private string selectSQL = @"SELECT {0} Users.Personid, Users.Firstname, Users.Lastname, Users.EmailAddress, Users.Password, Users.MailRQ, Users.ActivationCode, Users.Sex, Users.Disabled
                                     {1}
                                     FROM Users {2} {3}";

        public User GetUser(string emailadress)
        {
            User res = null;
            IDataReader reader = null;
            string where = " WHERE Users.Disabled = 0 AND Users.EmailAddress = @EMAILADDRESS AND ((ActivationCode is null and ResetPassword is null) or (ActivationCode is not null and ResetPassword is not null))";
            string sql = String.Format(selectSQL, "", "", where, "");
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@EMAILADDRESS", DbType.String, emailadress);
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        res = CreateUser(reader);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public User GetUserFromPass(string password)
        {
            User res = null;
            IDataReader reader = null;
            string where = " WHERE Users.Disabled = 0 AND Users.Password = @PASSWORD AND ((ActivationCode is null and ResetPassword is null) or (ActivationCode is not null and ResetPassword is not null))";
            string sql = String.Format(selectSQL, "", "", where, "");
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@PASSWORD", DbType.String, password);
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        res = CreateUser(reader);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public User GetUser(string email, string password, bool encripted)
        {
            User res = null;
            IDataReader reader = null;
            string where = " WHERE Users.Disabled = 0 AND Users.EmailAddress = @EMAILADDRESS AND Users.Password = @Password AND ((ActivationCode is null and ResetPassword is null) or (ActivationCode is not null and ResetPassword is not null))";
            string sql = String.Format(selectSQL, "", "", where, "");
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@EMAILADDRESS", DbType.String, email);
                db.AddInParameter(cmd, "@PASSWORD", DbType.String, encripted ? password : Musicman.Common.Utilities.Converter.EncriptPassword(password));
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        res = CreateUser(reader);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public long? AuthenticateUser(string email, string password, bool encripted)
        {
            bool res = false;
            string sql = "SELECT Users.Personid FROM Users WHERE Users.Disabled = 0 AND Users.EmailAddress = @EMAILADDRESS AND Users.Password = @Password AND ((ActivationCode is null and ResetPassword is null) or (ActivationCode is not null and ResetPassword is not null))";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@EMAILADDRESS", DbType.String, email);
            db.AddInParameter(cmd, "@PASSWORD", DbType.String, encripted ? password : Musicman.Common.Utilities.Converter.EncriptPassword(password));
            return (long?)db.ExecuteScalar(cmd);
        }

        public User GetUser(long userID, bool loadStats)
        {
            User res = null;
            IDataReader reader = null;
            string additionalFields = "";
            string where = " WHERE Users.Personid = @PERSONID";
            if (loadStats)
            {
                additionalFields = " ,UserStats.NUMLOGSWEB,UserStats.NUMLOGSCLIENT,UserStats.LastLoggedDate,UserStats.TOTALFILES,UserStats.TOTALLENGTH";
                where = String.Format("INNER JOIN UserStats on Users.Personid = UserStats.Personid {0}", where);
            }

            string sql = String.Format(selectSQL, "", additionalFields, where, "");
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    if (reader.Read())
                    {
                        res = CreateUser(reader);
                        if (loadStats)
                        {
                            res.UserStats = CreateUserStat(reader, 9);
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public List<User> LoadUsers(int top, string where, string orderBy, bool loadStats, SelectParameter[] parameters)
        {
            List<User> res = new List<User>();
            string additionalFields = "";
            if (loadStats)
            {
                additionalFields = " ,UserStats.NUMLOGSWEB,UserStats.NUMLOGSCLIENT,UserStats.LastLoggedDate,UserStats.TOTALFILES,UserStats.TOTALLENGTH";
                where = String.Format("INNER JOIN UserStats on Users.Personid = UserStats.Personid {0}", where);
            }
            string strTop = top != 0 ? " TOP " + top : "";
            string sql = String.Format(selectSQL, strTop, additionalFields, where, orderBy);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);

                foreach (SelectParameter parameter in parameters)
                {
                    db.AddInParameter(cmd, parameter.ParameterName, parameter.DbType, parameter.Value);
                }

                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        User u = CreateUser(reader);
                        if (loadStats)
                        {
                            u.UserStats = CreateUserStat(reader, 9);
                        }
                        res.Add(u);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res; 
        }

        public User CreateUser(IDataReader reader)
        {
            User res = new User();
            res.Personid = reader.GetInt64(0);
            res.Firstname = reader.GetString(1);
            res.Lastname = reader.GetString(2);
            res.Emailaddress = reader.GetString(3);
            res.Password = reader.GetString(4);
            res.MailRequest = reader.GetBoolean(5);
            res.ActivationCode = reader.IsDBNull(6) ? null : (Guid?)reader.GetGuid(6);
            res.PersonSex = (Sex)reader.GetByte(7);
            res.Disabled = reader.GetBoolean(8);
            return res;
        }

        public UserStats CreateUserStat(IDataReader reader, int baseIndex)
        {
            UserStats res = new UserStats();
            res.TotalLogsWeb = reader.GetInt32(baseIndex);
            res.TotalLogsCient = reader.GetInt32(1 + baseIndex);
            res.LastLoggedDate = reader.GetDateTime(2 + baseIndex);
            res.TotalFilesDownloaded = reader.GetInt64(3 + baseIndex);
            res.TotalFilesLength = reader.GetInt64(4 + baseIndex);
            return res;
        }

        public string[] GetUserRoles(long userID)
        {
            string selectSQL = @"SELECT ur1.role FROM dbo.UserRoles ur1 where ur1.userID = @USERID";

            List<String> res = new List<String>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(selectSQL);
                db.AddInParameter(cmd, "@USERID", DbType.Int64, userID);
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        string role = reader.GetString(0);
                        res.Add(role);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            if (res != null)
            {
                return res.ToArray();
            }
            else
            {
                return null;
            }
        }

        public bool CheckEmail(string email)
        {
            string sql = "SELECT 1 FROM Users WHERE EmailAddress = @EMAILADDRESS";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@EMAILADDRESS", DbType.String, email);
            object res = db.ExecuteScalar(cmd);
            return res != null;
        }

        public byte[] LoadProfilePicture(long userID)
        {
            byte[] res = null;
            string selectSQL = "SELECT ProfilePicture FROM Users WHERE Personid = @PERSONID";
            IDataReader reader = null;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            try
            {
                reader = db.ExecuteReader(cmd);
                if (reader.Read() && !reader.IsDBNull(0))
                {
                    // reader.get
                    const int buffersize = 1024 * 16;
                    byte[] buffer = new byte[buffersize];
                    using (MemoryStream ms = new MemoryStream())
                    {
                        long offset = 0;
                        long count = reader.GetBytes(0, offset, buffer, 0, buffersize);
                        while (count > 0)
                        {
                            offset += count;
                            ms.Write(buffer, 0, buffersize);
                            count = reader.GetBytes(0, offset, buffer, 0, (int)count);
                        }
                        res = ms.ToArray();
                        ms.Close();
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                throw ex;
            }

            return res;
        }

        #endregion

        #region update

        public bool UpdateUser(User user)
        {
            bool res = true;
            string updateCmd = @"UPDATE Users SET Firstname = @FIRSTNAME,
                                                  Lastname = @LASTNAME,
                                                  Password = @PASSWORD,
                                                  MailRQ = @MAILRQ,
                                                  Sex = @SEX
                                 WHERE PERSONID = @PERSONID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateCmd);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, user.Personid);
            db.AddInParameter(cmd, "@FIRSTNAME", DbType.String, user.Firstname);
            db.AddInParameter(cmd, "@LASTNAME", DbType.String, user.Lastname);
            db.AddInParameter(cmd, "@PASSWORD", DbType.String, user.Password);
            db.AddInParameter(cmd, "@MAILRQ", DbType.Boolean, user.MailRequest);
            db.AddInParameter(cmd, "@SEX", DbType.Byte, (byte)user.PersonSex);
            res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }

        public bool UpdateUserStats(long userID, LoginType loginType)
        {
            string updateStatsSP = "dbo.UpdateUserStats";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(updateStatsSP);
            db.AddInParameter(cmd, "@PersonId", DbType.Int64, userID);
            db.AddInParameter(cmd, "@web", DbType.Int32, loginType == LoginType.Web ? 1 : 0);
            db.AddInParameter(cmd, "@client", DbType.Int32, loginType == LoginType.Client ? 1 : 0);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool ResetUserPassword(string email, string password)
        {
            Guid activationCode = Guid.NewGuid();
            string updateSql = @"UPDATE Users SET ActivationCode = @ACTIVATIONCODE,
                                                  ResetPassword = @RESETPASSWORD 
                                 WHERE EmailAddress = @EMAILADDRESS";
            
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@ACTIVATIONCODE", DbType.Guid, activationCode);
            db.AddInParameter(cmd, "@RESETPASSWORD", DbType.String, Musicman.Common.Utilities.Converter.EncriptPassword(password));
            db.AddInParameter(cmd, "@EMAILADDRESS", DbType.String, email);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool ResetUserPassword(Guid activationCode)
        {
            string updateSql = @"UPDATE Users SET ActivationCode = null,
                                                  Password = ResetPassword, 
                                                  ResetPassword = null 
                                 WHERE ActivationCode = @ACTIVATIONCODE";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@ACTIVATIONCODE", DbType.Guid, activationCode);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool UpdateProfilePicture(long userID, byte[] picture)
        {
            string updateSql = @"UPDATE Users SET ProfilePicture = @IMAGEDATA WHERE Personid = @PERSONID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@IMAGEDATA", DbType.Binary, picture);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool UpdateLastWallVisit(long userID, DateTime date)
        {
            string updateSql = @"UPDATE Users SET LastWallVisit = @LASTVISIT WHERE Personid = @PERSONID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@LASTVISIT", DbType.DateTime, date);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool ActivateUser(string activationCode)
        {
            string updateSql = @"UPDATE Users SET ActivationCode = NULL WHERE ActivationCode = @ACTIVATIONCODE";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@ACTIVATIONCODE", DbType.String, activationCode);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        public bool ToggleEnabled(long userID, bool enabled)
        {
            string updateSql = @"UPDATE Users SET Disabled = @DISABLED WHERE Personid = @PERSONID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@DISABLED", DbType.Boolean, !enabled);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        #endregion

        #region insert
        private DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSql = @"INSERT INTO dbo.Users (Firstname,Lastname,EmailAddress,Password,MailRQ,ActivationCode,Sex)
                                                     VALUES (@Firstname,@Lastname,@EmailAddress,@Password,@MailRQ,@ActivationCode,@Sex) 
                                    SET @Personid = SCOPE_IDENTITY()";
            DbCommand cmd = db.GetSqlStringCommand(insertSql);
            db.AddInParameter(cmd, "@Firstname", DbType.String);
            db.AddInParameter(cmd, "@Lastname", DbType.String);
            db.AddInParameter(cmd, "@EmailAddress", DbType.String);
            db.AddInParameter(cmd, "@Password", DbType.String);
            db.AddInParameter(cmd, "@MailRQ", DbType.Boolean);
            db.AddInParameter(cmd, "@ActivationCode", DbType.Guid);
            db.AddInParameter(cmd, "@Sex", DbType.Byte);
            db.AddOutParameter(cmd, "@Personid", DbType.Int64, 8);
            return cmd;
        }

        private void SetInsertParameters(SqlDatabase db, DbCommand cmd, User user)
        {
            db.SetParameterValue(cmd, "@Firstname", user.Firstname);
            db.SetParameterValue(cmd, "@Lastname", user.Lastname);
            db.SetParameterValue(cmd, "@EmailAddress", user.Emailaddress);
            db.SetParameterValue(cmd, "@Password", Musicman.Common.Utilities.Converter.EncriptPassword(user.Password));
            db.SetParameterValue(cmd, "@MailRQ", user.MailRequest);
            db.SetParameterValue(cmd, "@ActivationCode", user.ActivationCode);
            db.SetParameterValue(cmd, "@Sex", user.PersonSex);
        }

        public bool InsertUser(User user)
        {
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertParameters(db, cmd, user);
            bool res = db.ExecuteNonQuery(cmd) == 1;
            user.Personid = (long)db.GetParameterValue(cmd, "@Personid");
            return res;
        }

        public bool AddUserToRole(long userID, string role)
        {
            string insrtRoleSQL = "INSERT INTO dbo.UserRoles (UserID, Role) VALUES (@UserID, @Role)";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(insrtRoleSQL);
            db.AddInParameter(cmd, "@UserID", DbType.Int64, userID);
            db.AddInParameter(cmd, "@Role", DbType.String, role);
            bool res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }

        #endregion

        #region delete

        public bool DeleteUser(long userID)
        {
            string deleteCmd = "dbo.DeleteUser";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(deleteCmd);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            return db.ExecuteNonQuery(cmd) >= 1;
        }

        #endregion 
    }
}
